<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mcampaign extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    
    public function camPaignPlace($number,$offset,$id, $id_place)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT u.id AS uid,u.fullname AS fullname, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end, c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid, cp.id_campaign, cp.id_place
                FROM campaign AS c
                INNER JOIN `user` AS u ON c.id_customers = u.id
                INNER JOIN campaign_place AS cp ON c.id = cp.id_campaign
                WHERE 1 = 1 AND cp.id_place = ".$id_place;
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND c.title like '%".$key."%' ";
        }
        $sql .= " ORDER BY c.id ASC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
    
    public function placeDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT s.shopName, s.address, s.mobile, s.logo, s.ip, s.lat, s.`long`, s.MaC, cp.id, cp.id_place, cp.id_campaign, s.id AS ssid, IF(cp.id_place IS NOT NULL, Count(s.id), 0) AS num FROM campaign_place AS cp LEFT JOIN campaign ON cp.id_campaign = campaign.id RIGHT JOIN shop AS s ON cp.id_place = s.id WHERE 1 = 1 GROUP BY s.id ";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (s.shopName like '%".$key."%' OR ";
            $sql .= " s.address like '%".$key."%') ";
        }
        $sql .= " ORDER BY s.shopName ASC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
        
    }
    
    public function getCampaignDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT u.id AS uid, u.fullname AS fullname, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 AND c.active = 2 ";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
    
	
	public function getCampaignAllDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT u.id AS uid, u.fullname AS fullname, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 ";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
	
	
	public function getCampaignStopDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT u.id AS uid, u.fullname AS fullname, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 AND c.active = 3 ";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
	
	
	public function getCampaignCancelDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT u.id AS uid, u.fullname AS fullname, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 AND c.active = 4 ";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
	
	public function dataPlaceInfo($id){
		return $this->db->query('SELECT id, shopName, address FROM shop WHERE id ='.$id)->row_object();
	}
	
    public function getCampaignPenddingDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT u.id AS uid, u.fullname AS fullname, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 AND c.active = 1";
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
        
    }
    
    public function getCampaignsendDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT u.id AS uid, c.code_campaign, c.id_customers, c.title, DATE_FORMAT(c.time_start, '%d-%m-%Y') AS time_start, DATE_FORMAT(c.time_end, '%d-%m-%Y') AS time_end,c.link_campaign, c.note, c.folder_campaign, c.active, c.created, c.id_agency, u.username, c.id AS cid FROM campaign AS c INNER JOIN `user` AS u ON c.id_customers = u.id WHERE 1=1 AND c.active = 1 AND c.id_agency = ".$id;
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
        
    }
    
	public function getListActive($id)
    {
        $data_status = array( '0' => 'Chưa gửi duyệt', '1' => 'Đang chờ duyệt', '2' => 'Đang chạy( Duyệt )', '3' => 'Đang tạm dừng', '4' => 'Hủy bỏ' );
        //$html = '<option value = "0">Chọn trạng thái</option>';
        $html = '';
        foreach($data_status AS $el=>$le)
        {
            if($el == $id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$el.'" '.$selected.'>'.$le.'</option>';
        }
        return $html;
    }
	
	public function getListModem($nas_id){
		$data = $this->db->query('SELECT * FROM modem WHERE 1=1 ORDER BY id DESC')->result_object(); //return $data;
		$html = '';
        foreach($data AS $el=>$le)
        {
            if($le->nas_id == $nas_id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$le->nas_id.'" '.$selected.'>Modem '.$le->name.' ( '.$le->nas_id.' )</option>';
        }
        return $html;
	}
	
	public function getCampaignInfo($id){
		return array('data' => $this->db->query('SELECT c.*, u.fullname FROM campaign AS c JOIN user AS u ON c.id_customers = u.id  WHERE c.id = '.$id)->row_object(), 'place' => $this->db->query('SELECT * FROM campaign_place WHERE id_campaign = '.$id)->result_object()) ;
	}
    
    public function updateCampaign($title,$time_start,$time_end,$active,$old_active,$link_campaign,$folder_campaign,$id,$oneview,$link_redirect){
        if( (int)$active == 2 ){
            $data = array (
                    'title' => $title,
                    'time_start' => $time_start,
                    'time_end' => $time_end,
                    'active' => $active,
                    'status_old' => $old_active,
                    'link_campaign' => $link_campaign,
                    'folder_campaign' => $folder_campaign,
                    'id_update' => $this->session->userdata('id_user'),
                    'oneview' => (int)$oneview,
                    'link_redirect' => $link_redirect
                    );
        }else{
            $data = array (
                    'title' => $title,
                    'time_start' => $time_start,
                    'time_end' => $time_end,
                    'active' => $active,
                    'status_old' => $old_active,
                    'id_update' => $this->session->userdata('id_user'),
                    'oneview' => (int)$oneview
                    );
        };
        $this->db->update('campaign', $data , "id = ".$id);
        return 200;
        
    }
    
	public function addPlace($shopName, $address, $mobile, $ip, $lat, $long, $nas_id){
		$data = array(
			'shopName' => $shopName,
			'address' => $address,
			'mobile' => $mobile,
			'ip' => $ip,
			'lat' => $lat,
			'long' => $long,
			'MaC' => $nas_id
		);
		//die(print_r($data));
		$this->db->insert('shop', $data);
		
		return 200;
	}
	
	public function getPlaceInfo($id){
		return $this->db->query('SELECT * FROM shop WHERE id = '.$id)->row_object();
	}
    
	public function editPlace($shopName, $address, $mobile, $ip, $lat, $long, $nas_id, $id){
		$data = array(
			'shopName' => $shopName,
			'address' => $address,
			'mobile' => $mobile,
			'ip' => $ip,
			'lat' => $lat,
			'long' => $long,
			'MaC' => $nas_id
		);
		$this->db->update('shop', $data , "id = ".$id);
        return 200;
	}
    
    public function getListModemDB($number,$offset,$id)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT * FROM modem WHERE 1=1 ";
        /*$key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (c.title like '%".$key."%' OR ";
            $sql .= " c.code_campaign like '%".$key."%') ";
        }
        $sql .= " ORDER BY c.id DESC"; 
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();*/
        $xs = $this->db->query($sql)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
	
    public function addModem($name,$nas_id){
        $data = array( 'name' => $name, 'nas_id' => $nas_id );
        $this->db->insert('modem', $data);
		return 200;
    }
    
    public function getModemInfo($id){
		return $this->db->query('SELECT * FROM modem WHERE id = '.$id)->row_object();
	}
    
    public function editModem($name, $nas_id, $id){
        $data = array ('name' => $name, 'nas_id' => $nas_id);
        $this->db->update('modem', $data , "id = ".$id);
        return 200;
    }

    
}